package DBLayer;

import Model.Invoice;
import Utils.TimeFormat;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class DbInvoice implements DbInterfaceInvoice {
	private Connection db_con;

    public DbInvoice() {
        db_con = DbConnection.getInstance().getDBcon();
    }

	public ArrayList<Invoice> getAllInvoices() {
		return miscWhere("");
	}

	public Invoice searchByNo(String no) {
		String wClause = "no = " + no;
        return singleWhere(wClause);
	}

	public int insertInvoice(Invoice inv) {
        int result = -1;
        String query = "INSERT INTO invoices(no, price) VALUES"
                + "('" + inv.getId() + "', '" + inv.getAmount() + "')";
        System.out.println("insert: " + query);
        try {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex) {
            System.out.println("Insert exception in invoice db: " + ex);
        }
        return result;
    }

	private ArrayList<Invoice> miscWhere(String wClause) {
        ResultSet results;
        ArrayList<Invoice> list = new ArrayList<Invoice>();

        String query = buildQuery(wClause);

        try { // read the product from the database
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;
            while (results.next()) {
                Invoice invObj = new Invoice();
                invObj = buildInvoice(results);
                list.add(invObj);
            }//end while
            stmt.close();
        } catch (Exception e) {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return (list.size() > 0 ? list : null);
    }

	private Invoice singleWhere(String wClause) {
        ResultSet results;
        Invoice invObj = null;
        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            if (results.next()) {
                invObj = buildInvoice(results);
                stmt.close();
            }

        } catch (Exception e) {
            System.out.println("Query exception: " + e);
        }
        return invObj;
    }

	//method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT * FROM invoices";

        if (wClause.length() > 0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //method to build an invoice object
    private Invoice buildInvoice(ResultSet results) {
        Invoice invObj = new Invoice();
        try {
            invObj.setId(Integer.parseInt(results.getString(1)));
			invObj.setPaymentDate(TimeFormat.string_to_gregorian(results.getString(2)));
			invObj.setAmount(Double.parseDouble(results.getString(3)));
        } catch (Exception e) {
            System.out.println("error in building the invoice object");
			e.printStackTrace();
        }
        return invObj;
    }
}
